﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using ModelLayer;


namespace DbLayer
{
    public class RecipeDb
    {

        private SqlUtility sqlutility;
        private SqlConnection con;

        public RecipeDb()
        {
            sqlutility = new SqlUtility();
        }
        ////Insert a recipe in database
        public int InsertRecipe(Recipe _recipe)
        {
            const string query = "Insert into Recipe(name, typeOf, servings, photoId, summary) values(@name, @typeOf, @servings, @photoId, @summary)";
            using (SqlConnection con = sqlutility.GetCon())
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@name", _recipe.Name);
                    cmd.Parameters.AddWithValue("@typeOf", _recipe.TypeOf);
                    cmd.Parameters.AddWithValue("@servings", _recipe.Servings);
                    cmd.Parameters.AddWithValue("photoId", _recipe.PhotoesId);
                    cmd.Parameters.AddWithValue("@summary", _recipe.Summary);
                    return sqlutility.ExecuteData(cmd, con);
                }
            }
        }
        private Recipe BuildRecipe(SqlDataReader _rdr)
        {
            Recipe temRecipe = new Recipe();
            temRecipe.Id = sqlutility.GetSafeInt(_rdr, 0);
            temRecipe.Name = sqlutility.GetSafeString(_rdr, 1);
            temRecipe.TypeOf = sqlutility.GetSafeString(_rdr, 2);
            temRecipe.Servings = sqlutility.GetSafeInt(_rdr, 3);
            temRecipe.PhotoesId = sqlutility.GetSafeInt(_rdr, 4);
            temRecipe.Summary = sqlutility.GetSafeString(_rdr, 5);
            return temRecipe;
        }
        public List<Recipe> GetRecipeByType(string _typeOf)
        {
            List<Recipe> recipeList = new List<Recipe>();
            using (con = sqlutility.GetCon())
            {
                con.Open();
                const string query = "Select * from Recipe where typeOf=@typeOf";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@typeOf",_typeOf);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Recipe temRecipe = new Recipe();
                            temRecipe = BuildRecipe(rdr);
                            recipeList.Add(temRecipe);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return recipeList;
        }
        public List<Recipe> GetAllergiFriendlyRecipies(List<Allergies> allergies)
        {
            string allergiselection = "";
            foreach(Allergies a in allergies)
            {
                allergiselection += a.Id + ", ";
            }
            
            if(allergiselection.Length > 0)
            {
                allergiselection = allergiselection.Substring(0, allergiselection.Length - 2);

            }
                
            List<Recipe> recipeList = new List<Recipe>();
            using (con = sqlutility.GetCon())
            {
                con.Open();
                string select = "select * from Recipe join recipeallergies on Recipe.id = Recipeallergies.recipeId join Allergies on Recipeallergies.allergiesId = Allergies.id";
                string condition = "where not allergies.id in(" + allergiselection + ")";
                string query = select + " " + condition;

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Recipe temRecipe = new Recipe();
                            temRecipe = BuildRecipe(rdr);
                            recipeList.Add(temRecipe);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return recipeList;
        }
        public List<Recipe> GetRecipeByName(string _name)
        {
            List<Recipe> recipeList = new List<Recipe>();
            using (con = sqlutility.GetCon())
            {
                con.Open();
                const string query = "select * from Recipe where name like @name";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@name", "%" + _name + "%");

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Recipe temRecipe = new Recipe();
                            temRecipe = BuildRecipe(rdr);
                            recipeList.Add(temRecipe);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return recipeList;
        }
        //TO DO: VIRKER IKKE
        public List<Recipe> GetRecipeByAllergies(List<Allergies> _list)
        {
            List<Recipe> retList = new List<Recipe>();
            List<int> allergiesIdList = new List<int>();
            foreach(Allergies a in _list)
            {
                if(a != null)
                { 
                using(SqlConnection con = sqlutility.GetCon())
                {
                    con.Open();
                    const string query = "select * from RecipeAllergies where allergiesId=@id";

                    using(SqlCommand cmd = new SqlCommand(query, con))
                    {
                        cmd.Parameters.AddWithValue("@id", a.Id);

                        using(SqlDataReader rdr = cmd.ExecuteReader())
                        {
                            while(rdr.Read())
                            {
                                int temInt = new int();
                                temInt = (int)rdr["recipeId"];
                                allergiesIdList.Add(temInt);
                            }//end while
                            rdr.Close();
                            rdr.Dispose();
                        }//end using rdr
                        cmd.Dispose();
                    }//end using cmd
                    con.Close();
                    con.Dispose();
                }//end con
                }
            }//end foreach

            foreach(int i in allergiesIdList)
            {
                using(SqlConnection con = sqlutility.GetCon())
                {
                    con.Open();
                    const string query = "select * from Recipe where id=@id";

                    using(SqlCommand cmd = new SqlCommand(query, con))
                    {
                        cmd.Parameters.AddWithValue("@id", i);

                        using(SqlDataReader rdr = cmd.ExecuteReader())
                        {
                            while(rdr.Read())
                            {
                                Recipe temRecipe = new Recipe();
                                temRecipe = BuildRecipe(rdr);
                                retList.Add(temRecipe);
                            }//end while
                            rdr.Close();
                            rdr.Dispose();
                        }//end using rdr
                        cmd.Dispose();
                    }//end using cmd
                    con.Close();
                    con.Dispose();
                }//end using con
            }//end foreach
            return retList;
        }
        public Recipe GetRecipeById(int id)
        {
            Recipe retRecipe = null;
            using (con = sqlutility.GetCon())
            {
                con.Open();
                const string query = "select * from Recipe where id=@id";

                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@id", id);

                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {                     
                            retRecipe = BuildRecipe(rdr);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return retRecipe;
        }
        private List<Recipe> SortRecipeList(List<Recipe> _list)
        {
            List<Recipe> retList = _list.Distinct().ToList();
            return retList;   
        }
        public List<Recipe> SelectAllRecipe()
        {
            List<Recipe> retList = new List<Recipe>();
            using(SqlConnection con = sqlutility.GetCon())
            {
                con.Open();
                const string query = "Select * from Recipe";

                using 
                (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using(SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            Recipe temRecipe = new Recipe();
                            temRecipe = BuildRecipe(rdr);
                            retList.Add(temRecipe);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            return retList;
        }

    }
}
 